Relational databases are well known today. They typically comprise tables organized into rows and columns. The first column lists “reference” items for respective rows, for example, names of specific customers in a table containing customer information. The “top” row identifies the type of data or “content item” for each row entry in the column, for example, the date of last update of the customer name, a hash value for the customer name and the customer's zip code, whether the customer name has been updated since it was first inserted, etc. As another example, the first column lists the names of employees as reference items in an table containing employee information, and the top row lists salary and last date of update of the salary as content items. The “inner” fields of both table include the actual data corresponding to each combination of reference item and content item. Specific data fields in each tables are accessed by referencing the corresponding row containing the reference item and the column containing the content item of interest.
Structure Query Language (“SQL”) is a standard programming language for program code that writes data into (i.e. inserting, adding or deleting data) the data fields, and reads data from the data fields (and also selects a data field or column, or finds a data field or column). SQL is also used to add, delete or change a reference item in the first column or add, delete or change a content item in the top row. Although SQL is both an ANSI and ISO standard, many database programs include nonstandard extensions to the standard SQL commands.
Some of the columns of a table in a relational database can be interrelated as a “family” or a “grouping”. In the example of the first table described above, when a row for a new customer is inserted or a customer name in an existing row is updated, the date of last update of the customer name, and hash value of the customer name and zip code should also be updated. In this first example, the customer name is a “parent” column, the date of last update is a dependent column and the hash value is a dependent column, all in the same family. In the example of the second table described above, when an employee is hired, the employee's salary will change (to the starting salary) as well as the date of update of the salary. In this second example, the name of the employee is a “parent” column, the salary is a “dependent” column and the date of last update is a dependent column, all in the same family.
The general term for an action(s) to perform, such as to update a dependent column, in response to a change to a parent column of the same family is “trigger program” or “trigger” for short. The trigger can automatically cause updates to the dependent columns, automatically generate or transform values for inserted or updated rows in the dependent table, or invoke functions to issue alerts in response to changes to the parent table. The trigger can be executed, in response to a request to change a parent column, shortly before an insert, update or deletion to the parent column or shortly after an insert, update or deletion to the parent column. A trigger can be written in SQL.
It was known to write a SQL program function that automatically updates a dependent column when there is a request to update its parent column. While this is effective to automatically update the dependent column, it was burdensome for a person to write this trigger program function and errors were made in the writing of the trigger program function, especially when there were a large number of parent and dependent columns and a large number of trigger programs to write.
Accordingly, an object of the present invention is to facilitate the process of generating a trigger program.